package com.cpit.qualitycontrol.dao.jpqlquery;


import com.cpit.qualitycontrol.entity.SubTaskReport;
import com.github.pagehelper.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * Created by fff on 2019/2/27.
 */
@Component
public class SubTaskReportRepository {

    @Autowired
    EntityManagerFactory entityManagerFactory;
    /**
     * select st.device_id, dvc.name,  stbd.device_position, dvc.version, device_type.name, process_rule.additional_business_id,   st.create_task_time  ,
     * stbd.latitude,stbd.longitude, st.organization_team_name,st.audit_count, project.name, st.sub_task_state ,
     * st.assign_task_time , st.end_task_time,st.description
     * FROM  qc_sub_task as st, qc_sub_task_business_data as stbd ,device as dvc ,device_type, process_rule, work_order_type, qc_work_order,project,product_line
     * <p>
     * where st.work_order_id=stbd.work_order_id
     * and  st.sub_task_id=stbd.sub_task_id
     * and  st.device_id=dvc.id
     * and dvc.device_type_id=device_type.id
     * and work_order_type.id= process_rule.work_order_type_id
     * and qc_work_order.work_order_id = st.work_order_id
     * and dvc.project_id = project.id
     * and  product_line.id=dvc.product_line_id ;
     */
    private static String strSqlFromJoin = " FROM SubTask st , SubTaskBusinessData stbd ,DeviceType dt , " +
            "Device d , Project p , ProductLine pl , WorkOrderType wot,ProcessRule pr ,SubTaskProcessInfo stpi " +
//            ", Organization o " +
            "where  st.workOrderId = stbd.workOrderId " +
            "and st.subTaskId = stbd.subTaskId " +
            "and st.deviceId = d.id " +
            "and d.deviceTypeId = dt.id " +
            "and d.projectId = p.id " +
            "and d.productLineId = pl.id " +
//            "and st.currentOrganizationTeamId = o.id " +
            " and st.workOrderTypeId = wot.id  " +
            " and  pr.workOrderTypeId= wot.id " +
             " and st.workOrderTypeId = pr.workOrderTypeId  "  +
             " and d.deviceTypeId = pr.deviceTypeId "  +
            "and stpi.subTaskId=st.subTaskId "+
            "and stpi.taskProcessInfoId = (select max(x.taskProcessInfoId) from  SubTaskProcessInfo x where x.subTaskId= stpi.subTaskId  and   x.processName='施工') ";

/*  SELECT a .sub_task_id, a.operator_name, a.ui_input_name ,
CASE WHEN ISNULL( a.ui_input_name)  THEN a.operator_name
     WHEN a.ui_input_name='' THEN a.operator_name
ELSE ui_input_name  end as builder
 from qc_sub_task_process_info a WHERE  a.task_process_info_id=(SELECT max(task_process_info_id)
  from qc_sub_task_process_info WHERE sub_task_id=a.sub_task_id);

 */
//    public SubTask querySubTaskById(Long subTaskId) {
//        EntityManager entityManager = entityManagerFactory.createEntityManager();
//
//        String strJpqlSql = getSubTaskJpqlSqlFixCondition().append(" and st.subTaskId = " + subTaskId).toString();
//        TypedQuery<SubTask> typedQuery = entityManager.createQuery(strJpqlSql, SubTask.class);
//
//        return typedQuery.getSingleResult();
//    }

    public Page<SubTaskReport> findByCondition(String beginTime, String endTime, Long productLineId, String projectName, String constructionTeamNames, Long deviceTypeId, Long workOrderType, Pageable pageable) {


        EntityManager entityManager = entityManagerFactory.createEntityManager();


        //查询记录总数
        String strJpqlCountSql = getSubTaskJpqlCountSqlFixCondition().append(getSubTaskSqlPartOfCondition(beginTime, endTime, productLineId,
                projectName, constructionTeamNames, deviceTypeId, workOrderType)).toString();
        Query query = entityManager.createQuery(strJpqlCountSql);

        Long recordCount = (Long) query.getSingleResult();


        String strJpqlSql = getSubTaskJpqlSqlFixCondition().append(getSubTaskSqlPartOfCondition(beginTime, endTime, productLineId,
                projectName, constructionTeamNames, deviceTypeId, workOrderType))
                .append(getSqlPartOfOrderBy(pageable)).toString();
        TypedQuery<SubTaskReport> typedQuery = entityManager.createQuery(strJpqlSql, SubTaskReport.class);


        typedQuery.setMaxResults(pageable.getPageSize());   //计数从0开始
        typedQuery.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());

        return new PageImpl<>(typedQuery.getResultList(), pageable, recordCount);
    }

    /**
     *
     * @param beginTime
     * @param endTime
     * @param productLineId
     * @param projectName
     * @param constructionTeamNames
     * @param deviceTypeId
     * @param workOrderType

     * @return
     */
    public List<SubTaskReport> findByCondition(String beginTime, String endTime, Long productLineId, String projectName, String constructionTeamNames, Long deviceTypeId, Long workOrderType) {
        EntityManager entityManager = entityManagerFactory.createEntityManager();
        String strJpql = getSubTaskJpqlSqlFixCondition().append(getSubTaskSqlPartOfCondition(beginTime, endTime, productLineId,
                projectName, constructionTeamNames, deviceTypeId, workOrderType))
               .toString();
        TypedQuery<SubTaskReport> typedQuery = entityManager.createQuery(strJpql, SubTaskReport.class);

        return typedQuery.getResultList();
    }


    /**
     * 生成动态可变条件部分字符串
     *
     * @param beginTime
     * @param endTime
     * @param productLineId
     * @param projectName
     * @param constructionTeamNames
     * @param deviceTypeId
     * @param workOrderType
     * @return
     */
    private StringBuilder getSubTaskSqlPartOfCondition(String beginTime, String endTime, Long productLineId, String projectName, String constructionTeamNames, Long deviceTypeId, Long workOrderType) {

        StringBuilder sbCondition = new StringBuilder("");
        if (productLineId != null && productLineId != 0) {
            sbCondition.append(" and pl.id = ").append(productLineId);
        }
        if (StringUtil.isNotEmpty(projectName)) {
            sbCondition.append(" and p.name like '%").append(projectName).append("%'");
        }
//        if(StringUtil.isEmpty(beginTime) ){
//            Date date = new Date();
//            SimpleDateFormat sdf  =new SimpleDateFormat("yyyy-MM-dd");
//            beginTime = sdf.format(date);
//        }
        if (StringUtil.isNotEmpty(beginTime)) {
            sbCondition.append(" and st.assignTaskTime > '").append(beginTime).append("' ");
        }
        if (StringUtil.isNotEmpty(endTime )) {
            sbCondition.append(" and st.assignTaskTime < '").append(endTime).append("' ");
        }
        if (StringUtil.isNotEmpty(constructionTeamNames)) {
            sbCondition.append(" and st.organizationTeamName like '%").append(constructionTeamNames).append("%'");
        }

        if (deviceTypeId != null && deviceTypeId != 0) {
            sbCondition.append(" and d.deviceTypeId = ").append(deviceTypeId);
        }
        if (workOrderType != null && workOrderType != 0) {
            sbCondition.append(" and pr.additionalBusinessId = ").append(workOrderType);
//            sbCondition.append(" and st.workOrderTypeId = pr.workOrderTypeId  ");
//            sbCondition.append(" and d.deviceTypeId = pr.deviceTypeId ");
        }
//          sbCondition.append("  group by d.id  ");
        return sbCondition;
    }


    /**
     * 生成含固定条件查询记录的JPQL语句
     *
     * @return JPQL语句字符串对象
     */
    private StringBuilder getSubTaskJpqlSqlFixCondition() {
        /**
         *  public SubTaskReport(String deviceId, String deviceName, String deviceAddrs,
         *  int workOrderType, String version, String devicetype, int additional_business_id,
         *  Double longitude, Double latitude, String constructionTeamNames, String projectName,
         *  String productLineName, Integer state, Date assignTime, Date endTime, String description)
         *
         *
         *  select st.device_id, dvc.name,  stbd.device_position, dvc.version, device_type.name, process_rule.additional_business_id,
         *  st.create_task_time  ,
         *  *  stbd.latitude,stbd.longitude, st.organization_team_name,st.audit_count, project.name, st.sub_task_state ,
         *  *  st.assign_task_time , st.end_task_time,st.description
         *
         *    st.auditCount
         */

        return new StringBuilder("SELECT distinct  new com.cpit.qualitycontrol.entity.SubTaskReport( d.id, d.name,d.addrs,pr.additionalBusinessId,  " +
                "d.version,  dt.name, stpi.operatorName,  stpi.uiInputName  ,   stbd.longitude, stbd.Latitude," +
                "st.organizationTeamName,  p.name, pl.name,  st.subTaskState  ,  st.assignTaskTime , st.endTaskTime,  stpi.description, "+
                "stbd.isInstall,stbd.isInstallDesc,stbd.faultDesc ,stbd.faultReason, stbd.isRecove, stbd.acquisitionMode ,stbd.rsrp"+
                ",stbd.sinr ,stbd.cellid ) "
                + strSqlFromJoin   );
    }


    /**
     * 生成含固定条件查询记录数量的JPQL语句
     *
     * @return JPQL语句字符串对象
     */
    private StringBuilder getSubTaskJpqlCountSqlFixCondition() {
        return new StringBuilder("SELECT count( distinct st.subTaskId) " + strSqlFromJoin);
    }


    /**
     * 生成JPQL语句中排序部分
     *
     * @param pageable 含排序的分页对象Pageable对象
     * @return 排序部分字符串对象
     */
    private StringBuilder getSqlPartOfOrderBy(Pageable pageable) {
        final String STRING_ORDER_BY = " order by ";
        StringBuilder stringBuilder = new StringBuilder("");

        Sort sort = pageable.getSort();
        if (sort != null) {
            stringBuilder.append(STRING_ORDER_BY);
            Iterator<Sort.Order> iterator = sort.iterator();
            while (iterator.hasNext()) {
                Sort.Order order = iterator.next();
                if (order == null)
                    break;
                String orderProperty = order.getProperty();
                if ("subTaskName".equals(orderProperty))
                    stringBuilder.append("st.subTaskName");
                else if ("deviceId".equals(orderProperty))
                    stringBuilder.append("st.deviceId");
                else if ("deviceTypeId".equals(orderProperty))
                    stringBuilder.append("d.deviceTypeId");
                else if ("projectName".equals(orderProperty))
                    stringBuilder.append("p.projectName");
                else if ("workOrderTypeId".equals(orderProperty))
                    stringBuilder.append("st.workOrderTypeId");
                else if ("productLineName".equals(orderProperty))
                    stringBuilder.append("pl.productLineName");
                else if ("organizationTeamName".equals(orderProperty))
                    stringBuilder.append("st.organizationTeamName");
                else if ("subTaskState".equals(orderProperty))
                    stringBuilder.append("st.subTaskState");
                else if ("assignTaskTime".equals(orderProperty))
                    stringBuilder.append("st.assignTaskTime");
                else if ("endTaskTime".equals(orderProperty))
                    stringBuilder.append("st.endTaskTime");
                else if ("description".equals(orderProperty))
                    stringBuilder.append("st.description");
                else
                    stringBuilder.append("st.").append(orderProperty);
                stringBuilder.append(" ").append(order.getDirection().name()).append(",");
            }
        }
        if (stringBuilder.lastIndexOf(",") > 0)
            stringBuilder.setLength(stringBuilder.length() - 1);
        if (stringBuilder.length() == STRING_ORDER_BY.length())
            stringBuilder.setLength(0);

        return stringBuilder;
    }

}
